﻿CREATE PROCEDURE [dbo].[usp_ssiscatalog]
	@exec_id									BIGINT			=	NULL	
,	@action										NVARCHAR(MAX)	=	NULL	/*Default value will come from deprecated parameter @operation_type*/
,	@exec_execution								BIT				=	1		
,	@exec_events								BIT				=	1		
,	@exec_errors								BIT				=	1		
,	@exec_warnings								BIT				=	1		
,	@exec_executable_stats						BIT				=	1		
,	@exec_events_packagesexcluded				NVARCHAR(MAX)	=	NULL	
,	@execs_folder_name							sysname			=	NULL
,	@execs_project_name							sysname			=	NULL
,	@execs_package_name							sysname			=	NULL
,	@execs_executed_as_name						sysname			=	NULL
,	@execs_status_desc							NVARCHAR(MAX)	=	NULL
,	@execs_minimum_execution_id					BIGINT			=	NULL
,	@execs_maximum_execution_id					BIGINT			=	NULL
,	@configure_collect_usage_stats				BIT				=	NULL
,	@show_docs									BIT				=	1		/*Display documentation for the selected @action*/
,	@show_docs_only								BIT				=	0		/*Only display documentation, don't do anything else*/
,	@a											NVARCHAR(MAX)	=	NULL	/*Short form for @action*/
,	@operation_type								NVARCHAR(MAX)	=	N'exec'	
,	@ot											NVARCHAR(MAX)	=	NULL	/*Short form for @operation_type*/
,	@default_action								NVARCHAR(MAX)	=	NULL	/*The desired default action to be set when @action='set_parameter_defaults'*/
AS
BEGIN
		SET NOCOUNT ON;
		/*Use short form to override long form*/
		IF	(@ot IS NOT NULL) SET @operation_type = @ot; /*If a value is supplied for @ot it overrides @operation_type*/
		/*@operation_type has been replaced by @action, though we still need @operation_type for backward compatibility*/
		IF	(@action IS NULL) SET @action = @operation_type;
		/*Use short form to override long form*/
		IF	(@a IS NOT NULL) SET @action = @a; /*If a value is supplied for @a it overrides @action*/
		
		/*Log the fact that this sproc has been called*/
		DECLARE @vcollectStatsUsageIsEnabled BIT = (SELECT [sp_ssiscatalog].[collect_usage_stats_is_enabled]());
		IF (@vcollectStatsUsageIsEnabled=1)
		BEGIN
				DECLARE	@vexec_events_packages_excluded_isspecified		BIT		=	IIF(@exec_events_packagesexcluded IS NULL,0,1)			;
				DECLARE	@vexecs_folder_name_isspecified					BIT		=	IIF(@execs_folder_name IS NULL,0,1)						;
				DECLARE	@vexecs_project_name_isspecified				BIT		=	IIF(@execs_project_name IS NULL,0,1)					;
				DECLARE	@vexecs_package_name_isspecified				BIT		=	IIF(@execs_package_name IS NULL,0,1)					;
				DECLARE	@vexecs_executed_as_name_isspecified			BIT		=	IIF(@execs_executed_as_name IS NULL,0,1)				;
				DECLARE	@vexecs_status_desc_isspecified					BIT		=	IIF(@execs_status_desc IS NULL,0,1)						;
				DECLARE	@vexecs_maximum_execution_id_isspecified		BIT		=	IIF(@execs_maximum_execution_id IS NULL,0,1)			;
				DECLARE	@vexecs_minimum_execution_id_isspecified		BIT		=	IIF(@execs_minimum_execution_id IS NULL,0,1)			;
				EXEC [sp_ssiscatalog].[log_usage]
						@action											=	@action
					,	@exec_id										=	@exec_id									
					,	@exec_execution									=	@exec_execution								
					,	@exec_events									=	@exec_events								
					,	@exec_errors									=	@exec_errors								
					,	@exec_warnings									=	@exec_warnings								
					,	@exec_executable_stats							=	@exec_executable_stats						
					,	@exec_events_packages_excluded_isspecified		=	@vexec_events_packages_excluded_isspecified	
					,	@execs_folder_name_isspecified					=	@vexecs_folder_name_isspecified				
					,	@execs_project_name_isspecified					=	@vexecs_project_name_isspecified				
					,	@execs_package_name_isspecified					=	@vexecs_package_name_isspecified				
					,	@execs_executed_as_name_isspecified				=	@vexecs_executed_as_name_isspecified			
					,	@execs_status_desc_isspecified					=	@vexecs_status_desc_isspecified				
					,	@execs_maximum_execution_id_isspecified			=	@vexecs_maximum_execution_id_isspecified		
					,	@execs_minimum_execution_id_isspecified			=	@vexecs_minimum_execution_id_isspecified		
					,	@configure_collect_usage_stats					=	@configure_collect_usage_stats
					,	@show_docs										=	@show_docs									
					,	@show_docs_only									=	@show_docs_only								
					;
		END
		DECLARE @actions [sp_ssiscatalog].[actions];
		INSERT	@actions ([action]) VALUES ('exec'),('execs'),('configure'),(N'exec_created'),(N'exec_running'),(N'exec_canceled'),(N'exec_failed'),(N'exec_pending'),(N'exec_ended_unexpectedly'),(N'exec_succeeded'),(N'exec_stopping'),(N'exec_completed'),(N'set_parameter_defaults');

		IF (@show_docs = 1)
		BEGIN
				DECLARE @vActionValues NVARCHAR(MAX) = (SELECT STUFF((
															SELECT	''', '''+a.action
															FROM	@actions a
															FOR XML PATH('')
															),1,3,'')
														) + '''';
				DECLARE	@vDocs	NVARCHAR(MAX)	=	N'Parameters:
@action			NVARCHAR(MAX)	=	N''exec''	/*Specify what we actually want sp_ssiscatalog to do. Possible values are ' + @vActionValues + N'. Default is ''exec'' Short form is @a.*/
';
				EXEC	[sp_ssiscatalog].[show_docs]
							@docs	=	@vDocs;
		END
		BEGIN TRY
				
				IF NOT EXISTS (SELECT	*	FROM	@actions WHERE [action] = @action)
				BEGIN
						RAISERROR('%s is not a valid action',16,1,@action);
				END
				ELSE
				BEGIN
						IF (@action = N'set_parameter_defaults')
								EXEC	[sp_ssiscatalog].[set_parameter_defaults]
											@exec_id							=	@exec_id						
										,	@exec_execution						=	@exec_execution					
										,	@exec_events						=	@exec_events					
										,	@exec_errors						=	@exec_errors					
										,	@exec_warnings						=	@exec_warnings					
										,	@exec_executable_stats				=	@exec_executable_stats			
										,	@exec_events_packagesexcluded		=	@exec_events_packagesexcluded	
										,	@execs_folder_name					=	@execs_folder_name				
										,	@execs_project_name					=	@execs_project_name				
										,	@execs_package_name					=	@execs_package_name				
										,	@execs_executed_as_name				=	@execs_executed_as_name			
										,	@execs_status_desc					=	@execs_status_desc				
										,	@execs_minimum_execution_id			=	@execs_minimum_execution_id		
										,	@execs_maximum_execution_id			=	@execs_maximum_execution_id		
										,	@show_docs							=	@show_docs						
										,	@show_docs_only						=	@show_docs_only					
										,	@default_action						=	@default_action		
										,	@possible_actions					=	@actions;			
						IF (@action = N'exec')
								EXEC	[sp_ssiscatalog].[exec]	
											@exec_id									=	@exec_id
										,	@exec_execution								=	@exec_execution
										,	@exec_errors								=	@exec_errors
										,	@exec_events								=	@exec_events
										,	@exec_warnings								=	@exec_warnings
										,	@exec_executable_stats						=	@exec_executable_stats
										,	@exec_events_packagesexcluded				=	@exec_events_packagesexcluded
										,	@show_docs									=	@show_docs
										,	@show_docs_only								=	@show_docs_only		;
						IF (@action IN (N'exec_created',N'exec_running',N'exec_canceled',N'exec_failed',N'exec_pending',N'exec_ended_unexpectedly',N'exec_succeeded',N'exec_stopping',N'exec_completed'))
						BEGIN
								DECLARE @status_desc NVARCHAR(MAX) = REPLACE(@action,N'exec_',N'');
								DECLARE @status_id	int	=	(SELECT [status_id] FROM [sp_ssiscatalog].[get_status_from_status_desc](@status_desc))
								EXEC	[sp_ssiscatalog].[exec_most_recent_status]	
											@status_id									=	@status_id
										,	@exec_execution								=	@exec_execution
										,	@exec_errors								=	@exec_errors
										,	@exec_events								=	@exec_events
										,	@exec_warnings								=	@exec_warnings
										,	@exec_executable_stats						=	@exec_executable_stats
										,	@exec_events_packagesexcluded				=	@exec_events_packagesexcluded
										,	@show_docs									=	@show_docs
										,	@show_docs_only								=	@show_docs_only		;
						END
						IF (@action = N'execs')
								EXEC	[sp_ssiscatalog].[execs]	
											@execs_folder_name							=	@execs_folder_name		
										,	@execs_project_name							=	@execs_project_name		
										,	@execs_package_name							=	@execs_package_name		
										,	@execs_executed_as_name						=	@execs_executed_as_name	
										,	@execs_status_desc							=	@execs_status_desc		
										,	@execs_minimum_execution_id					=	@execs_minimum_execution_id
										,	@execs_maximum_execution_id					=	@execs_maximum_execution_id	
										,	@show_docs									=	@show_docs
										,	@show_docs_only								=	@show_docs_only		;
						IF (@action = N'configure')
								EXEC	[sp_ssiscatalog].[configure]
											@collect_usage_stats						=	@configure_collect_usage_stats	
										,	@show_docs									=	@show_docs
										,	@show_docs_only								=	@show_docs_only		;
				END
		END TRY
		BEGIN CATCH
				THROW;      
		END CATCH              
END
